drop table jms_dwd.dwd_breakoff_detail_reading;
CREATE TABLE jms_dwd.dwd_breakoff_detail_reading (
                                                         dt                        date COMMENT '日期',
                                                         billcode                  varchar(80) comment '运单号',
                                                         network_code              varchar(80) comment '机构编码',
                                                         order_source_code         varchar(80) comment '订单来源',
                                                         network_type                varchar(80) COMMENT '机构类型：1.中心，2.集散点，3.网点',
                                                         next_scan_type              varchar(80) COMMENT '下一机构类型：1.中心，2.集散点，3.网点',
                                                         read_time                 datetime comment '订阅时间',
                                                         taking_time               datetime comment '揽收时间',
                                                         network_name              varchar(300) comment '机构名称',
                                                         agent_code                varchar(80) comment '代理区',
                                                         agent_name                varchar(300) comment '代理区',
                                                         franchisee_code           varchar(80) comment '加盟商',
                                                         franchisee_name           varchar(300) comment '加盟商',
                                                         order_source_name         varchar(300) comment '订单来源',
                                                         breakoff_type             int comment '中断类型：0机构内，1机构间',
                                                         arrival_count             int comment '到件次数',
                                                         send_count                int comment '发件次数',
                                                         send_shipment             varchar(80) comment '发件任务号',
                                                         actual_departure_time     datetime comment '实际发车时间',
                                                         planned_departure_time    datetime comment '计划发车时间',
                                                         next_actual_arrival_time  datetime comment '下一站实际到件时间',
                                                         next_planned_arrival_time datetime comment '下一站规划到件时间',
                                                         actual_trans_min          double comment '实际运输时长（min）',
                                                         planned_trans_min         double comment '计划运输时长（min）',
                                                         delay_trans_min           double comment '运输延误时长（min）',
                                                         stop_min                  double comment '停留时长（min）',
                                                         stop_over_12              int comment '责任机构到件和发件时间间隔超12小时：1.到件、发件漏扫，2.到件漏扫，3.发件漏扫，4.超12小时，5.超12小时（负），6.未超12小时，7.其他',
                                                         customer_code             varchar(80) comment '客户编码',
                                                         customer_name             varchar(800) comment '客户名称',
                                                         breakoff_scan_type        varchar(80) comment '中断机构扫描类型',
                                                         breakoff_scan_time        datetime comment '中断机构扫描时间',
                                                         next_scan_code            varchar(80) comment '下一扫描环节网点',
                                                         next_scan_name            varchar(300) comment '下一扫描环节网点',
                                                         next_agent_code           varchar(80) comment '下一扫描环节代理区',
                                                         next_agent_name           varchar(300) comment '下一扫描环节代理区',
                                                         next_franchisee_code      varchar(80) comment '下一扫描环节加盟商',
                                                         next_franchisee_name      varchar(300) comment '下一扫描环节加盟商',
                                                         next_est_scantype         varchar(80) comment '下一扫描环节类型',
                                                         next_est_scantime         datetime comment '下一扫描环节时间',
                                                         end_code                  varchar(80) comment '目的网点',
                                                         end_name                  varchar(300) comment '目的网点',
                                                         breakoff_min              double comment '中断时间（min）超时类型'
    ) ENGINE=OLAP
    DUPLICATE KEY(`dt`, `billcode`,`network_code`,`order_source_code`,`network_type`,`next_scan_type`)
    COMMENT '平台中断明细表(订阅时间)'
    PARTITION BY RANGE(`dt`)
    (
PARTITION p20211016 VALUES [('2021-10-16'), ('2021-10-17')),
PARTITION p20211017 VALUES [('2021-10-17'), ('2021-10-18')),
PARTITION p20211018 VALUES [('2021-10-18'), ('2021-10-19')),
PARTITION p20211019 VALUES [('2021-10-19'), ('2021-10-20')),
PARTITION p20211020 VALUES [('2021-10-20'), ('2021-10-21')),
PARTITION p20211021 VALUES [('2021-10-21'), ('2021-10-22')),
PARTITION p20211022 VALUES [('2021-10-22'), ('2021-10-23')),
PARTITION p20211023 VALUES [('2021-10-23'), ('2021-10-24')),
PARTITION p20211024 VALUES [('2021-10-24'), ('2021-10-25')),
PARTITION p20211025 VALUES [('2021-10-25'), ('2021-10-26')),
PARTITION p20211026 VALUES [('2021-10-26'), ('2021-10-27')),
PARTITION p20211027 VALUES [('2021-10-27'), ('2021-10-28')),
PARTITION p20211028 VALUES [('2021-10-28'), ('2021-10-29')),
PARTITION p20211029 VALUES [('2021-10-29'), ('2021-10-30')),
PARTITION p20211030 VALUES [('2021-10-30'), ('2021-10-31')),
PARTITION p20211031 VALUES [('2021-10-31'), ('2021-11-01')),
PARTITION p20211101 VALUES [('2021-11-01'), ('2021-11-02')),
PARTITION p20211102 VALUES [('2021-11-02'), ('2021-11-03')),
PARTITION p20211103 VALUES [('2021-11-03'), ('2021-11-04')),
PARTITION p20211104 VALUES [('2021-11-04'), ('2021-11-05')),
PARTITION p20211105 VALUES [('2021-11-05'), ('2021-11-06')),
PARTITION p20211106 VALUES [('2021-11-06'), ('2021-11-07')),
PARTITION p20211107 VALUES [('2021-11-07'), ('2021-11-08')),
PARTITION p20211108 VALUES [('2021-11-08'), ('2021-11-09')),
PARTITION p20211109 VALUES [('2021-11-09'), ('2021-11-10')),
PARTITION p20211110 VALUES [('2021-11-10'), ('2021-11-11')),
PARTITION p20211111 VALUES [('2021-11-11'), ('2021-11-12')),
PARTITION p20211112 VALUES [('2021-11-12'), ('2021-11-13')),
PARTITION p20211113 VALUES [('2021-11-13'), ('2021-11-14')),
PARTITION p20211114 VALUES [('2021-11-14'), ('2021-11-15')),
PARTITION p20211115 VALUES [('2021-11-15'), ('2021-11-16')),
PARTITION p20211116 VALUES [('2021-11-16'), ('2021-11-17')),
PARTITION p20211117 VALUES [('2021-11-17'), ('2021-11-18')),
PARTITION p20211118 VALUES [('2021-11-18'), ('2021-11-19')),
PARTITION p20211119 VALUES [('2021-11-19'), ('2021-11-20')),
PARTITION p20211120 VALUES [('2021-11-20'), ('2021-11-21')),
PARTITION p20211121 VALUES [('2021-11-21'), ('2021-11-22')),
PARTITION p20211122 VALUES [('2021-11-22'), ('2021-11-23')),
PARTITION p20211123 VALUES [('2021-11-23'), ('2021-11-24')),
PARTITION p20211124 VALUES [('2021-11-24'), ('2021-11-25')),
PARTITION p20211125 VALUES [('2021-11-25'), ('2021-11-26')),
PARTITION p20211126 VALUES [('2021-11-26'), ('2021-11-27')),
PARTITION p20211127 VALUES [('2021-11-27'), ('2021-11-28')),
PARTITION p20211128 VALUES [('2021-11-28'), ('2021-11-29')),
PARTITION p20211129 VALUES [('2021-11-29'), ('2021-11-30')),
PARTITION p20211130 VALUES [('2021-11-30'), ('2021-12-01')),
PARTITION p20211201 VALUES [('2021-12-01'), ('2021-12-02')),
PARTITION p20211202 VALUES [('2021-12-02'), ('2021-12-03')),
PARTITION p20211203 VALUES [('2021-12-03'), ('2021-12-04')),
PARTITION p20211204 VALUES [('2021-12-04'), ('2021-12-05')),
PARTITION p20211205 VALUES [('2021-12-05'), ('2021-12-06')),
PARTITION p20211206 VALUES [('2021-12-06'), ('2021-12-07')),
PARTITION p20211207 VALUES [('2021-12-07'), ('2021-12-08')),
PARTITION p20211208 VALUES [('2021-12-08'), ('2021-12-09')),
PARTITION p20211209 VALUES [('2021-12-09'), ('2021-12-10')),
PARTITION p20211210 VALUES [('2021-12-10'), ('2021-12-11')),
PARTITION p20211211 VALUES [('2021-12-11'), ('2021-12-12')),
PARTITION p20211212 VALUES [('2021-12-12'), ('2021-12-13'))

    )
    DISTRIBUTED BY HASH(`billcode`) BUCKETS 10
    PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-100",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10",
    "in_memory" = "false",
    "storage_format" = "V2"
    );


ALTER TABLE jms_dwd.dwd_breakoff_detail_reading
    ADD COLUMN mage_region_code VARCHAR(80) KEY comment '管理大区编码' AFTER next_scan_type;

ALTER TABLE jms_dwd.dwd_breakoff_detail_reading
    ADD COLUMN mage_region_name VARCHAR(300)  comment '管理大区名称' AFTER mage_region_code;